package com.xingxuecn.demos.service;

import com.xingxuecn.demos.common.MyJdbcTemplate;
import com.xingxuecn.demos.domain.Menu;
import com.xingxuecn.demos.domain.Role;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

/**
 * @author Kris at 2018/9/27 15:35
 */
public class SpringJdbcDemo {
    static Logger logger = Logger.getLogger(SpringJdbcDemo.class);

    static MyJdbcTemplate myJdbcTemplate;
    static{
        ApplicationContext context = new ClassPathXmlApplicationContext("spring-application.xml","spring-db.xml");
        myJdbcTemplate  = context.getBean(MyJdbcTemplate.class);
    }

    public static void main(String[] args) {
        query();
        //add();
    }

    /**
     * 查询demos
     */
    public static void query(){
        //1.简单查询
        /*String sql = "select * from menu";
        List<Map<String, Object>> maps = myJdbcTemplate.queryForList(sql);*/

        //2.带参数
        String sql_p = "select * from menu where parent_id = ?";
        /*List<Map<String, Object>> maps1 = myJdbcTemplate.queryForList(sql_p, new Object[]{100});
        logger.info(maps1);*/

        //3.映射ＰＯＪＯ。这个queryForListMapper方法是我们自己扩展的。可以少些代码实现映射。
        List<Menu> menus = myJdbcTemplate.queryForListMapper(sql_p,Menu.class,new Object[]{100});
        logger.info(menus);
        List<Role> roles = myJdbcTemplate.queryForListMapper("select * from role",Role.class,null);
        logger.info(roles);

        //4.单列
        /*String sql_count = "select count(*) from menu ";
        Integer count = myJdbcTemplate.queryForObject(sql_count, Integer.class);
        logger.info(count);*/
    }

    /**
     * 修改，删除也用update
     */
    public static void update(){
        String sql = "update menu set parent_id = ? where id = ?";
        int rows = myJdbcTemplate.update(sql, new Object[]{200, 234});
        logger.info(rows);
    }


    /**
     * 新增，并且获取到主键
     * 注意：这个insert方法是我们自己扩展的。他会返回主键
     */
    public static void add(){
        final String sql = "insert into user(nick,account,pwd) values(?,?,?)";
        Number rows = myJdbcTemplate.insert(sql, new Object[]{"随便三", "suibiansan", "1234567"});
        logger.info(rows);
    }
}
